package demo.dao.impl;

import demo.dao.EmpDao;
import demo.entity.Dept;
import demo.entity.Emp;
import demo.entity.EmpChild;
import demo.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class EmpDaoImpl implements EmpDao {

    @Override
    public int insertEmp(Emp emp) throws SQLException {
        //创建一个QueryRunner对象
        QueryRunner queryRunner = new QueryRunner();
        Object[] objects = {emp.getEmpno(), emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno()};
        String sql = "insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(?,?,?,?,?,?,?,?)";
        Connection connection = JDBCUtils.getConnection();
        int inserter = queryRunner.update(connection, sql, objects);
        return inserter;
    }

    @Override
    public int updateEmp(Emp emp) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Object[] objects = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno(), emp.getEmpno()};
        String sql = "update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?";
        Connection connection = JDBCUtils.getConnection();
        int update = queryRunner.update(connection, sql, objects);
        return update;
    }

    @Override
    public int deleteEmp(Integer empno) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "delete from emp where empno=?";
        Connection connection = JDBCUtils.getConnection();
        int delete = queryRunner.update(connection, sql, empno);
        return delete;
    }

    @Override
    public List<Emp> queryAllEmp() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from emp";
        List<Emp> empList = queryRunner.query(sql, new BeanListHandler<Emp>(Emp.class));
        return empList;
    }

    @Override
    public Emp queryEmpById(Integer empno) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from emp where empno = ?";
        Emp emp = queryRunner.query(sql, new BeanHandler<>(Emp.class), empno);
        return emp;
    }

    @Override
    public Long queryCount() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select count(*) from emp";//"select count(1) from emp"
        Long result = queryRunner.query(JDBCUtils.getConnection(), sql, new ScalarHandler<Long>());
        return result;
    }

    @Override
    public List<EmpChild> queryEmpChildAll() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select empno,ename,job,mgr,hiredate,sal,comm,dept.deptno,dname,location from emp left join dept on emp.deptno = dept.deptno";
        return queryRunner.query(sql, resultSet -> {
            List<EmpChild> list = new ArrayList<>();
            while (resultSet.next()) {
                EmpChild empChild = new EmpChild();
                empChild.setEmpno(resultSet.getInt("empno"));
                empChild.setEname(resultSet.getString("ename"));
                empChild.setJob(resultSet.getString("job"));
                empChild.setMgr(resultSet.getInt("mgr"));
                empChild.setHiredate(resultSet.getDate("hiredate"));
                empChild.setSal(resultSet.getDouble("sal"));
                empChild.setComm(resultSet.getDouble("comm"));
                empChild.setDeptno(resultSet.getInt("deptno"));
                Dept dept = new Dept();
                dept.setDeptno(resultSet.getInt("deptno"));
                dept.setDname(resultSet.getString("dname"));
                dept.setLocation(resultSet.getString("location"));
                empChild.setDept(dept);
                list.add(empChild);
            }
            return list;
        });
    }
}
